import 'package:floor/floor.dart';
import 'package:share_fit/app/dao/dao_mixin.dart';
import 'package:share_fit/app/models/home/stress_model.dart';

@dao
abstract class StressDao with DaoMixin<StressModel> {
  @override
  @Query('SELECT * FROM stress WHERE _id = :id')
  Stream<StressModel?> findById(int id);

  @Query('SELECT * FROM stress where address = :address and calendar = :calendar order by calendar_time desc limit 1')
  Future<StressModel?> findLatestOne(String address, String calendar);

  @override
  @Query('SELECT * FROM stress')
  Future<List<StressModel>> findAll();

  @override
  @Query('SELECT * FROM stress')
  Stream<List<StressModel>> findAllAsStream();

  @override
  @Query('delete FROM stress')
  Future<void> deleteAll();
  
  @Query('SELECT * FROM stress WHERE date_created >= :startTime and date_created < :endTime')
  Future<List<StressModel>> findBy(int startTime, int endTime);

  @Query("SELECT calendar, CAST(AVG(pressure_value) AS INT) as pressure_value from ("
            "SELECT pressure_value, STRFTIME('%Y-%m-%d %H', calendar_time) as calendar FROM stress where address = :address and calendar = :calendar "
        ") t "
        "GROUP BY calendar order by calendar")
  Future<List<StressModel>> groupByMinute(String address, String calendar);

  @Query("SELECT STRFTIME('%Y-%m-%d', calendar_time) as calendar, CAST(AVG(pressure_value) AS INT) as pressure_value "
      "FROM stress where address = :address and calendar >= :start and calendar <= :end "
      "group by calendar order by calendar")
  Future<List<StressModel>> findInRange(String address, String start, String end);

  /// 查找指定月份中有压力数据的日期
  @Query("select distinct calendar from stress "
      "where address = :address and STRFTIME('%Y', calendar_time) >= :minYear and STRFTIME('%Y', calendar_time) <= :maxYear")
  Future<List<String>> findHasRecordDays(String address, String minYear, String maxYear);

  @Query('SELECT pressure_value FROM stress where address = :address and calendar >= :start and calendar <= :end')
  Future<List<StressModel>> findDataDetail(String address, String start, String end);

  @Query('delete FROM stress where address = :address')
  @override
  Future<void> deleteAllByAddress(String address);
}